How to model JSON data in a Go app with CockroachDB

*Guest post alert! Jack is a core maintainer of pgx, a PostgreSQL driver and toolkit for Go. He helped build the testing integration for CockroachDB and pgx. Jack blogs at https://www.jackchristensen.com/*

-–

Many applications benefit from representing some data in a relational structure and some data in a more flexible document structure like JSON. Taking advantage of the JSON functionality available in a relational database can reduce the need for a dedicated object database, minimize infrastructure and application complexity, and improve performance.

Modern SQL databases such as PostgreSQL and CockroachDB support both models with native support for storing, building, and manipulating JSON documents. The Go language and the pgx database driver also include functionality for working with relational and document data.

For this example, we will model JSON data in a simple Go app that uses CockroachDB as its datastore. We will use a simplified products table for a shopping application. Every product has a SKU and a name. But there are many other attributes of a product such as color, size, weight, format, and capacity that only apply to some products. We will use one column for all these extra attributes.

create table products (
	sku text primary key,
	name text not null,
	extra_attributes jsonb not null
);

insert into products (sku, name, extra_attributes)
values
	('A1000-BK', 'A1000 Battery', '{"color": "black", "capacity": 4000}'),
	('A1200-RD', 'A1200 Battery', '{"color": "red", "capacity": 5500}'),
	('TBLTCASE-10', 'Tablet Case', '{"size": "10 inch", "material": "Leather"}')
;

The following code snippets will assume a database connection is already established. See https://www.cockroachlabs.com/docs/stable/build-a-go-app-with-cockroachdb for more information on connection setup.

We can use a string or a []byte to read or write a database jsonb type. Then we can use the encoding/json package to convert it to and from our application data type. However, with pgx that isn’t necessary. pgx can automatically marshal and unmarshal values into JSON.

	type Product struct {
		SKU             string
		Name            string
		ExtraAttributes map[string]interface{}
	}

	// ...

	var product Product
	err = conn.QueryRow(
		context.Background(),
		"select * from products where sku = $1",
		"A1000-BK",
	).Scan(&product.SKU, &product.Name, &product.ExtraAttributes)
	if err != nil {
		// handle error
	}

	fmt.Printf("%#v\n", product.ExtraAttributes) // => map[string]interface {}{"capacity":4000, "color":"black"}

A map[string]interface{} can handle any JSON object, but may be a bit awkward to work with. If we know the object structure ahead of time we can read directly into a Go struct.

	type BatteryAttributes struct {
		Color    string `json:"color"`
		Capacity int32  `json:"capacity"`
	}

	type Battery struct {
		SKU             string
		Name            string
		ExtraAttributes BatteryAttributes
	}

	// ...

	err = conn.QueryRow(
		context.Background(),
		"select * from products where sku = $1",
		"A1000-BK",
	).Scan(&battery.SKU, &battery.Name, &battery.ExtraAttributes)
	if err != nil {
		// handle error
	}

	fmt.Printf("%#v\n", battery.ExtraAttributes)
  // => BatteryAttributes{Color:"black", Capacity:4000}

We can also build JSON documents from relational data directly in the database. For example we can use the jsonb_agg and jsonb_build_object functions to build a JSON document that lists all products.

	var buf []byte
	err = conn.QueryRow(
		context.Background(),
		"select jsonb_agg(jsonb_build_object('sku', sku, 'name', name)) from products",
	).Scan(&buf)
	if err != nil {
		// handle error
	}

	fmt.Println(string(buf))
  // => [{"name": "A1000 Battery", "sku": "A1000-BK"}, {"name": "A1200 Battery", "sku": "A1200-RD"}, {"name": "Tablet Case", "sku": "TBLTCASE-10"}]

This approach can both simplify the Go layer and improve performance.

In summary, taking advantage of the JSON functionality available in a relational database like CockroachDB can reduce the need for a dedicated object database, minimize infrastructure and application complexity, and improve performance.